﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using Oracle.DataAccess.Client;
using System.Linq;
using System.Web;

namespace ISHotel.Database 
{
    [Serializable]
    public class HostTable : DbTable
    {
        public static String TABLE_NAME = "Host";

        public String SQL_SELECT = "SELECT * FROM Host ORDER BY prijmeni ASC";
        public String SQL_SELECT_rodne_cislo = "SELECT * FROM Host WHERE rodne_cislo=:rodne_cislo";
        public String SQL_INSERT = "INSERT INTO Host (rodne_cislo, jmeno, prijmeni, ulice, mesto, psc, telefon, email) VALUES (:rodne_cislo, :jmeno, :prijmeni, :ulice, :mesto, :psc, :telefon, :email)";
        public String SQL_UPDATE = "UPDATE Host SET jmeno=:jmeno, prijmeni=:prijmeni, ulice=:ulice, mesto=:mesto, psc=:psc, telefon=:telefon, email=:email WHERE rodne_cislo=:rodne_cislo";
        public String SQL_DELETE = "DELETE FROM Host WHERE rodne_cislo=:rodne_cislo";

        public HostTable()
            : base(TABLE_NAME)
        {
        }

                /**
         * Insert the record.
         **/
        public int Insert(Host Host)
        {
            mDatabase.Connect();
            OracleCommand command = mDatabase.CreateCommand("projekt_add_host");
            command.CommandType = CommandType.StoredProcedure;
            PrepareCommand(command, Host);
            int ret;
            mDatabase.BeginTransaction();
            try
            {
                ret = mDatabase.Insert(command);
                mDatabase.EndTransaction();
            }
            catch (Exception e)
            {
                mDatabase.Rollback();
                throw e;
            }
            mDatabase.Close();
            return ret;
        }

        /**
         * Update the record.
         **/
        public int Update(Host Host)
        {
            mDatabase.Connect();
            OracleCommand command = mDatabase.CreateCommand(SQL_UPDATE);

            command.Parameters.Add(new OracleParameter(":" + Host.ATTR_jmeno, OracleDbType.Varchar2, Host.jmeno.Length));
            command.Parameters[":" + Host.ATTR_jmeno].Value = Host.jmeno;

            command.Parameters.Add(new OracleParameter(":" + Host.ATTR_prijmeni, OracleDbType.Varchar2, Host.prijmeni.Length));
            command.Parameters[":" + Host.ATTR_prijmeni].Value = Host.prijmeni;

            command.Parameters.Add(new OracleParameter(":" + Host.ATTR_ulice, OracleDbType.Varchar2, Host.ulice.Length));
            command.Parameters[":" + Host.ATTR_ulice].Value = Host.ulice;

            command.Parameters.Add(new OracleParameter(":" + Host.ATTR_mesto, OracleDbType.Varchar2, Host.mesto.Length));
            command.Parameters[":" + Host.ATTR_mesto].Value = Host.mesto;

            command.Parameters.Add(new OracleParameter(":" + Host.ATTR_psc, OracleDbType.Decimal));
            command.Parameters[":" + Host.ATTR_psc].Value = Host.psc;

            command.Parameters.Add(new OracleParameter(":" + Host.ATTR_telefon, OracleDbType.Decimal));
            command.Parameters[":" + Host.ATTR_telefon].Value = Host.telefon;

            if (Host.email != null)
            {
                command.Parameters.Add(new OracleParameter(":" + Host.ATTR_email, OracleDbType.Varchar2, Host.email.Length));
                command.Parameters[":" + Host.ATTR_email].Value = Host.email;
            }
            else
            {
                command.Parameters.Add(new OracleParameter(":" + Host.ATTR_email, OracleDbType.Varchar2, 15));
                command.Parameters[":" + Host.ATTR_email].Value = "NOT PROVIDED";
            }

            command.Parameters.Add(new OracleParameter(":" + Host.ATTR_rodne_cislo, OracleDbType.Varchar2, Host.rodne_cislo.Length));
            command.Parameters[":" + Host.ATTR_rodne_cislo].Value = Host.rodne_cislo;

            int ret = mDatabase.Update(command);
            mDatabase.Close();
            return ret;
        }

        /**
         * Prepare a command.
         **/

        private void PrepareCommand(OracleCommand command, Host Host)
        {

            command.Parameters.Add(new OracleParameter(":" + Host.ATTR_rodne_cislo, OracleDbType.Varchar2, Host.rodne_cislo.Length));
            command.Parameters[":" + Host.ATTR_rodne_cislo].Value = Host.rodne_cislo;

            command.Parameters.Add(new OracleParameter(":" + Host.ATTR_jmeno, OracleDbType.Varchar2, Host.jmeno.Length));
            command.Parameters[":" + Host.ATTR_jmeno].Value = Host.jmeno;

            command.Parameters.Add(new OracleParameter(":" + Host.ATTR_prijmeni, OracleDbType.Varchar2, Host.prijmeni.Length));
            command.Parameters[":" + Host.ATTR_prijmeni].Value = Host.prijmeni;

            command.Parameters.Add(new OracleParameter(":" + Host.ATTR_ulice, OracleDbType.Varchar2, Host.ulice.Length));
            command.Parameters[":" + Host.ATTR_ulice].Value = Host.ulice;

            command.Parameters.Add(new OracleParameter(":" + Host.ATTR_mesto, OracleDbType.Varchar2, Host.mesto.Length));
            command.Parameters[":" + Host.ATTR_mesto].Value = Host.mesto;

            command.Parameters.Add(new OracleParameter(":" + Host.ATTR_psc, OracleDbType.Decimal));
            command.Parameters[":" + Host.ATTR_psc].Value = Host.psc;

            command.Parameters.Add(new OracleParameter(":" + Host.ATTR_telefon, OracleDbType.Decimal));
            command.Parameters[":" + Host.ATTR_telefon].Value = Host.telefon;

            command.Parameters.Add(new OracleParameter(":" + Host.ATTR_email, OracleDbType.Varchar2, Host.email.Length));
            command.Parameters[":" + Host.ATTR_email].Value = Host.email;
            
            }

        /**
         * Select records.
         **/
        public Collection<Host> Select()
        {
            mDatabase.Connect();
            OracleCommand command = mDatabase.CreateCommand(SQL_SELECT);
            OracleDataReader reader = mDatabase.Select(command);

            Collection<Host> Hosts = Read(reader);
            reader.Close();
            mDatabase.Close();
            return Hosts;
        }

        /**
         * Select the record.
         **/
        public Host Select(String rodne_cislo)
        {
            mDatabase.Connect();
            OracleCommand command = mDatabase.CreateCommand(SQL_SELECT_rodne_cislo);

            command.Parameters.Add(new OracleParameter(":" + Host.ATTR_rodne_cislo, OracleDbType.Varchar2));
            command.Parameters[":" + Host.ATTR_rodne_cislo].Value = rodne_cislo;
            OracleDataReader reader = mDatabase.Select(command);

            Collection<Host> Hosts = Read(reader);
            Host Host1 = null;
            if (Hosts.Count == 1)
            {
                Host1 = Hosts[0];
            }
            reader.Close();
            mDatabase.Close();
            return Host1;
        }

        private Collection<Host> Read(OracleDataReader reader)
        {
            Collection<Host> Hosts = new Collection<Host>();

            while (reader.Read())
            {
                Host Host = new Host();
                Host.rodne_cislo = reader.GetString(0);
                Host.jmeno = reader.GetString(1);
                Host.prijmeni = reader.GetString(2);
                Host.ulice = reader.GetString(3);
                Host.mesto = reader.GetString(4);
                Host.psc = (int)reader.GetDecimal(5);
                if (!reader.IsDBNull(6))
                    if ((int)reader.GetDecimal(6) != 0)
                        Host.telefon = (int)reader.GetDecimal(6);
                if (!reader.IsDBNull(7))
                    if (reader.GetString(7) != "NOT PROVIDED")
                        Host.email = reader.GetString(7);
                Hosts.Add(Host);
            }
            return Hosts;
        }

        /**
         * Delete the record.
         */
        public int Delete(String rodne_cislo)
        {
            mDatabase.Connect();
            OracleCommand command = mDatabase.CreateCommand(SQL_DELETE);

            command.Parameters.Add(new OracleParameter(":" + Host.ATTR_rodne_cislo, OracleDbType.Varchar2));
            command.Parameters[":" + Host.ATTR_rodne_cislo].Value = rodne_cislo;
            int ret;
            try
            {
                ret = mDatabase.Delete(command);
            }
            catch (Exception e)
            {
                throw e;
            }
            mDatabase.Close();
            return ret;
        }
    }
}